/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseLayer;

import controller.IndexController;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.NamingException;

import movieBean.*;

/**
 *
 * @author Rigsby Chi
 */
public class MoviesLookUp {

    public static void main(String arg[]) {
        Movie movie[] = MoviesLookUp.getAllMovies("2013-05-03 00:00:00", "2013-05-03 23:59:59");
        for (int i = 0; i < movie.length; i++) {
            System.out.println("Name: " + movie[i].getName() + "  Des: " + movie[i].getDescription() + "  Num: " + movie[i].getNoOfShowTime());
            MovieSlot[] ms = movie[i].getMovieSlot();
            for (int j = 0; j < ms.length; j++) {
                System.out.println("Time: " + ms[j].getStartTime() + "  House: " + ms[j].getHouse() + "  Price: " + ms[j].getPrice() + "  ID: " + ms[j].getSlotID());
            }
            System.out.println("Image: " + movie[i].getThumbnailPath() + "  Tra: " + movie[i].getTrailerPath() + "\n");
        }
        
        Movie aMovie = MoviesLookUp.getMovie("Movie1", "2013-05-03 00:00:00", "2013-05-03 23:59:59");
        if(aMovie==null){
            System.out.println("A movie is null");
        } else {
            System.out.println("Name: " + aMovie.getName() + "  Des: " + aMovie.getDescription() + "  Num: " + aMovie.getNoOfShowTime());
            MovieSlot[] ms = aMovie.getMovieSlot();
            for (int j = 0; j < ms.length; j++) {
                System.out.println("Time: " + ms[j].getStartTime() + "  House: " + ms[j].getHouse() + "  Price: " + ms[j].getPrice() + "  ID: " + ms[j].getSlotID());
            }
            System.out.println("Image: " + aMovie.getThumbnailPath() + "  Tra: " + aMovie.getTrailerPath() + "\n");
        }
        
        
        SeatPlan seatPlan = MoviesLookUp.getSeatPlan("Movie3+1");
        int noOfSeat = seatPlan.getNumberOfSeats();
        String seat[] = seatPlan.getSeat();
        System.out.println(noOfSeat);
        for (int i = 0; i < noOfSeat; i++) {
            if (!seat[i].equals("availableSeat")) {
                System.out.print("Seat " + i + ": " + seat[i] + "   ");
            }
        }
        System.out.print("\n");

        HouseSchedule houseSchedule = MoviesLookUp.getHouseSchedule(1);
        System.out.println(houseSchedule.getNumberOfSlot());
        for (int i = 0; i < houseSchedule.getNumberOfSlot(); i++) {
            System.out.println(houseSchedule.getStartTime()[i] + "  " + houseSchedule.getDuration()[i]);
        }

        System.out.println(MoviesLookUp.isMovieSlotEmpty("Movie1+1"));
        System.out.println(MoviesLookUp.isMovieSlotEmpty("Movie1+2"));
        System.out.println(MoviesLookUp.isMovieSlotEmpty("Movie2+1"));
        System.out.println(MoviesLookUp.isMovieSlotEmpty("Movie3+1"));
    }

    public static Movie[] getAllMovies(String time1, String time2) {
        Movie movie[] = null;

        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            PreparedStatement getMovieStatement = con.prepareStatement("SELECT * FROM Movie", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet movieRS = getMovieStatement.executeQuery();
            int numberOfMovie = 0;
            if (movieRS == null || !movieRS.next()) {
                return null;
            } else {
                movieRS.last();
                numberOfMovie = movieRS.getRow();
                movieRS.beforeFirst();

                movie = new Movie[numberOfMovie];
                for (int i = 0; movieRS.next(); i++) {
                    movie[i] = new Movie();
                    movie[i].setName(movieRS.getString("NAME"));
                    movie[i].setDescription(movieRS.getString("DESCRIPTION"));
                    movie[i].setDuration(movieRS.getInt("DURATION"));
                    movie[i].setThumbnailPath(movieRS.getString("PICPATH"));
                    movie[i].setTrailerPath(movieRS.getString("TRAILERPATH"));

                    MovieSlot[] movieSlot;

                    PreparedStatement getSlotStatement = con.prepareStatement("SELECT * FROM Movieslot WHERE MOVIENAME=? AND SLOTSTATE=1 AND starttime BETWEEN ? AND ? ORDER BY starttime", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    getSlotStatement.setString(1, movieRS.getString("NAME"));
                    getSlotStatement.setString(2, time1);
                    getSlotStatement.setString(3, time2);
                    ResultSet slotRS = getSlotStatement.executeQuery();
                    
                    if (slotRS == null || !slotRS.next()) {
                        movie[i].setNoOfShowTime(0);
                        movie[i].setMovieSlot(null);
                    } else {
                        slotRS.last();
                        int numberOfShow = slotRS.getRow();
                        slotRS.beforeFirst();

                        movie[i].setNoOfShowTime(numberOfShow);
                        
                        movieSlot = new MovieSlot[numberOfShow];
                        for (int j = 0; slotRS.next(); j++) {
                            movieSlot[j] = new MovieSlot();
                            movieSlot[j].setHouse(slotRS.getInt("HID"));
                            movieSlot[j].setPrice(slotRS.getInt("PRICE"));
                            movieSlot[j].setSlotID(slotRS.getString("SLOTID"));
                            movieSlot[j].setStartTime(slotRS.getString("STARTTIME"));
                        }
                        movie[i].setMovieSlot(movieSlot);
                    }
                    if (slotRS != null) {
                        slotRS.close();
                    }
                    if (getSlotStatement != null) {
                        getSlotStatement.close();
                    }
                }
            }
            if (movieRS != null) {
                movieRS.close();
            }
            if (getMovieStatement != null) {
                getMovieStatement.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
            movie = null;
        } catch (NamingException e) {
            e.printStackTrace();
            movie = null;
        }
        return movie;
    }
    
    public static Movie getMovie(String movieName, String time1, String time2) {
        Movie movie = null;

        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            PreparedStatement getMovieStatement = con.prepareStatement("SELECT * FROM Movie where name = ? order by name", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getMovieStatement.setString(1, movieName);
            
            ResultSet movieRS = getMovieStatement.executeQuery();

            if (movieRS == null || !movieRS.next()) {
                return null;
            } else {

                movie= new Movie();
                movie.setName(movieRS.getString("NAME"));
                movie.setDescription(movieRS.getString("DESCRIPTION"));
                movie.setDuration(movieRS.getInt("DURATION"));
                movie.setThumbnailPath(movieRS.getString("PICPATH"));
                movie.setTrailerPath(movieRS.getString("TRAILERPATH"));

                MovieSlot[] movieSlot;

                PreparedStatement getSlotStatement = con.prepareStatement("SELECT * FROM Movieslot WHERE MOVIENAME=? AND SLOTSTATE=1 AND starttime BETWEEN ? AND ? ORDER BY starttime", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                getSlotStatement.setString(1, movieName);
                getSlotStatement.setString(2, time1);
                getSlotStatement.setString(3, time2);
                ResultSet slotRS = getSlotStatement.executeQuery();

                if (slotRS == null || !slotRS.next()) {
                    movie.setNoOfShowTime(0);
                    movie.setMovieSlot(null);
                } else {
                    slotRS.last();
                    int numberOfShow = slotRS.getRow();
                    slotRS.beforeFirst();

                    movie.setNoOfShowTime(numberOfShow);

                    movieSlot = new MovieSlot[numberOfShow];
                    for (int j = 0; slotRS.next(); j++) {
                        movieSlot[j] = new MovieSlot();
                        movieSlot[j].setHouse(slotRS.getInt("HID"));
                        movieSlot[j].setPrice(slotRS.getInt("PRICE"));
                        movieSlot[j].setSlotID(slotRS.getString("SLOTID"));
                        movieSlot[j].setStartTime(slotRS.getString("STARTTIME"));
                    }
                    movie.setMovieSlot(movieSlot);
                }

                if (slotRS != null) {
                    slotRS.close();
                }
                if (getSlotStatement != null) {
                    getSlotStatement.close();
                }
            }
            if (movieRS != null) {
                movieRS.close();
            }
            if (getMovieStatement != null) {
                getMovieStatement.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
            movie = null;
        } catch (NamingException e) {
            e.printStackTrace();
            movie = null;
        }
        return movie;
    }

    public static SeatPlan getSeatPlan(String slotID) {
        SeatPlan seatPlan = new SeatPlan();

        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            int numberOfSeat = 0;
            int seat[];

            PreparedStatement pstmt = con.prepareStatement("SELECT seat FROM house, movieslot WHERE slotid=? AND movieslot.hid=house.hid");
            pstmt.setString(1, slotID);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs != null && rs.next()) {
                numberOfSeat = rs.getInt("seat");
                seatPlan.setNumberOfSeat(numberOfSeat);
                seat = new int[numberOfSeat];

                pstmt = con.prepareStatement("SELECT seatno, moneyused FROM buyrecord WHERE (RECORDSTATE=0 or RECORDSTATE=1) and slotid=?");
                pstmt.setString(1, slotID);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    String seatNo = rs.getString("seatno");
                    for (int i = 0; i < seatNo.length(); i = i + 3) {
                        seat[Integer.parseInt(seatNo.substring(i, i + 3)) - 1] = 1;
                    }
                }

                pstmt = con.prepareStatement("SELECT seat FROM SeatDisable WHERE slotid=?");
                pstmt.setString(1, slotID);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    seat[rs.getInt("seat") - 1] = -1;
                }

                seatPlan.setSeat(seat);
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException c) {
            c.printStackTrace();
        }
        return seatPlan;
    }

    public static HouseSchedule getHouseSchedule(int hid) {
        HouseSchedule houseSchedule = new HouseSchedule();

        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            int numberOfSchedule = 0;
            int duration[];
            String startTime[];

            PreparedStatement pstmt = con.prepareStatement("SELECT starttime, duration FROM movieslot,movie WHERE SLOTSTATE=1 AND moviename=name AND hid=? order by starttime", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstmt.setInt(1, hid);

            ResultSet rs = pstmt.executeQuery();

            if (rs != null && rs.next()) {
                rs.last();
                numberOfSchedule = rs.getRow();
                rs.beforeFirst();

                houseSchedule.setNumberOfSlot(numberOfSchedule);

                duration = new int[numberOfSchedule];
                startTime = new String[numberOfSchedule];
                for (int i = 0; rs.next(); i++) {
                    duration[i] = rs.getInt("duration");
                    startTime[i] = rs.getString("starttime");
                }

                houseSchedule.setDuration(duration);
                houseSchedule.setStartTime(startTime);
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException c) {
            c.printStackTrace();
        }
        return houseSchedule;
    }

    public static boolean isMovieSlotEmpty(String slotID) {
        boolean result = false;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT count(*) from buyrecord where slotid=? and (recordstate=0 or recordstate=1)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, slotID);

            ResultSet rs = pstmt.executeQuery();

            if (rs != null && rs.next()) {
                result = (rs.getInt(1) == 0);
            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException c) {
            c.printStackTrace();
        }
        return result;
    }
    
    public static String getMovieSlotStartTime(String slotID){
        String startTime = null;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT starttime from movieslot where slotid=?");
            pstmt.setString(1, slotID);

            ResultSet rs = pstmt.executeQuery();

            if (rs != null && rs.next()) {
                startTime = rs.getString("starttime");
            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException c) {
            c.printStackTrace();
        }
        return startTime;
    }
}